﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Controls;
using FasterWPF;
using System.Windows;

namespace WPFContribDataAccess
{
    //NOTE: THIS DOES NOT PROTECT AGAINST SQL INJECTION ATTACKS. EXAMPLE IS MORE FOR SMALL INTRANET APPS BEHIND A FIREWALL!!!

    public class OleDBDataAccess : ISimpleDataAccess
    {
        private SQLParms _sqlParms = null;

        public SQLParms SQLParms
        {
            get { return _sqlParms; }
            set { _sqlParms = value; }
        }

        public OleDBDataAccess(SQLParms sqlParms)
        {
            _sqlParms = sqlParms;
        }

        //CONNECTION STRING FOR EXCEL
        //@"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"

        public void SetConnection()
        {
            if (_sqlParms.SQLConn == null)
            {
                _sqlParms.SQLConn = new System.Data.OleDb.OleDbConnection
                    (_sqlParms.SQLConnectionString);
            }
        }

        public void CloseConnection()
        {
            if (_sqlParms.SQLConn != null)
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Close();
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Dispose();
                _sqlParms.SQLConn = null;
            }
        }

        public void ExecuteQuery(string txtQuery)
        {
            try
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Open();
                _sqlParms.SQLCmd = (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).CreateCommand();
                (_sqlParms.SQLCmd as System.Data.OleDb.OleDbCommand).CommandText = txtQuery;
                (_sqlParms.SQLCmd as System.Data.OleDb.OleDbCommand).ExecuteNonQuery();
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {

                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Close();
            }
        }

        public void LoadData(string tableName, string offset, string increment, Action<DataRow> loadFrameworkElement)
        {            
            try
            {
                _sqlParms.DS.Clear();
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Open();
                _sqlParms.SQLCmd = (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).CreateCommand();
                string CommandText = "SELECT * FROM " + tableName;
                _sqlParms.DB = new System.Data.OleDb.OleDbDataAdapter(CommandText, (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection));
                _sqlParms.DS.Reset();
                 DataTable dt= new DataTable();
                 foreach (string colName in _sqlParms.ColumnNames)
                 {
                     dt.Columns.Add(new DataColumn(colName));
                 }
                _sqlParms.DS.Tables.Add(dt);
                (_sqlParms.DB as System.Data.OleDb.OleDbDataAdapter).Fill(Int32.Parse(offset), Int32.Parse(increment), _sqlParms.DS.Tables[0]);
                _sqlParms.DT = _sqlParms.DS.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Close();
            }

            foreach (DataRow dr in _sqlParms.DT.Rows)
            {
                loadFrameworkElement(dr);
            }
        }

        public void LoadSubsetOfNames(string columnName, string tableName, string commaDelimitedList, Action<DataRow> loadList)
        {
            try
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Open();
                _sqlParms.SQLCmd = (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).CreateCommand();
                string CommandText = "SELECT " + columnName + " from " + tableName + " WHERE ID IN ( " + commaDelimitedList + ")";
                _sqlParms.DB = new System.Data.OleDb.OleDbDataAdapter(CommandText, (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection));
                _sqlParms.DS.Reset();
                (_sqlParms.DB as System.Data.OleDb.OleDbDataAdapter).Fill(_sqlParms.DS);
                _sqlParms.DT = _sqlParms.DS.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Close();
            }

            foreach (DataRow dr in _sqlParms.DT.Rows)
            {
                loadList(dr);
            }
        }

        //public static void Add(SQLParms sqlParms)
        //{
        //    string txtSQLQuery = "insert into test (Name) values ('" + "hello" + "')";
        //    ExecuteQuery(sqlParms, txtSQLQuery);
        //}

        public long Count(string tableName)
        {
            try
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Open();
                _sqlParms.SQLCmd = (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).CreateCommand();
                string CommandText = "SELECT * FROM " + tableName ;
                _sqlParms.DB = new System.Data.OleDb.OleDbDataAdapter(CommandText, (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection));
                _sqlParms.DS.Reset();
                (_sqlParms.DB as System.Data.OleDb.OleDbDataAdapter).Fill(_sqlParms.DS);
                _sqlParms.DT = _sqlParms.DS.Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                (_sqlParms.SQLConn as System.Data.OleDb.OleDbConnection).Close();
            }

            return (long)(_sqlParms.DS.Tables[0].Rows.Count);
        }
    }
}
